<?php
error_reporting('IGNORE_REPEATED_ERRORS');

header('Content-type: application/excel');
header('Content-Disposition: attachment; filename="211QA_Records.csv"');
readfile('211QA_Records.csv');



mysql_connect("localhost", "root", "") or
    die("Could not connect: " . mysql_error());
mysql_select_db("QA");



$start_date = $_GET["start_date"];
$end_date = $_GET["end_date"];


if (strlen($start_date)<1) {
	$result = mysql_query("SELECT MIN(CALL_DATE) FROM QA.Caller");
	$row = mysql_fetch_array($result, MYSQL_BOTH);
	$start_date = $row[0];
}

if (strlen($end_date)<1) {
	$result = mysql_query("SELECT MAX(CALL_DATE) FROM QA.Caller");
	$row = mysql_fetch_array($result, MYSQL_BOTH);
	$end_date = $row[0];
}

echo "\"Report contains Unreachable, Needs and Agency call record information for the following date range:\"\n";
echo "Start Date, End Date\n";
echo $start_date.",".$end_date."\n\n";

$qstring = "select count(*) from QA.Caller where QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\"";
$result = mysql_query($qstring);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$calls_attempted = $row[0];

$qstring = "select count(*) from QA.Caller, QA.Unreachable where QA.Caller.Reference_Number=QA.Unreachable.Caller_Reference AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\"";
$result = mysql_query($qstring);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$calls_unreachable = $row[0];

echo "QA Statistics\n";
echo "Calls Attempted, Unreachable\n";
echo $calls_attempted.",".$calls_unreachable."\n\n";



echo "Unreachable Call Records\n";
$qstring = "SELECT QA.Caller.Call_Date , QA.Caller.Reference_Number, QA.Taxonomies.Taxonomy_Description, QA.Unreachable_Reasons.Reason_Description,  QA.Unreachable.Other  
FROM  QA.Caller, QA.Unreachable, QA.Taxonomies, QA.Unreachable_Reasons WHERE QA.Unreachable.Taxonomy = QA.Taxonomies.Reference_Number AND QA.Unreachable_Reasons.Reference_Number = QA.Unreachable.Reason AND
QA.Caller.Reference_Number=QA.Unreachable.Caller_Reference AND
QA.Taxonomies.Reference_Number=QA.Unreachable.Taxonomy AND QA.Caller.Call_Date BETWEEN  \"".$start_date."\" AND \"".$end_date."\" ORDER BY QA.Caller.Call_Date ASC";
$result = mysql_query($qstring);
//echo "Query: ".$qstring."\n";

echo "Call Date, Caller Reference Number, Taxonomy, Unreachable Reason, Other Reason\n";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
	echo $row[0].",".$row[1].",".$row[2].",".$row[3].",".$row[4]."\n";
}

mysql_free_result($result);
echo "\n";


echo "Needs Call Records\n";
$qstring = "SELECT QA.Caller.Call_Date, QA.Caller.Reference_Number, QA.Taxonomies.Taxonomy_Description, QA.Needs.Met, QA.211Feedback.Appropriate, QA.Refer.Refer_Description, QA.211Feedback.Comments
FROM  QA.Caller, QA.Taxonomies, QA.Needs, QA.211Feedback, QA.Refer WHERE QA.211Feedback.Refer=QA.Refer.Reference_Number AND QA.Needs.Taxonomy = QA.Taxonomies.Reference_Number
AND QA.Caller.Reference_Number=QA.Needs.Caller_Reference AND QA.Taxonomies.Reference_Number=QA.Needs.Taxonomy AND QA.Caller.Reference_Number=QA.211Feedback.Caller_Reference
AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\" ORDER BY QA.Caller.Call_Date ASC";
$result = mysql_query($qstring);
//echo "Query: ".$qstring."\n";

echo "Call Date, Caller Reference Number, Taxonomy, Need Met, 211 Appropriate, 211 Would Refer, 211 Comments \n";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {

	if($row[3]==1){
		$three="Yes";
	}else{
		$three="No";
	}

	if($row[4]==1){
		$four="Yes";
	}else{
		$four="No";
	}
	echo $row[0].",".$row[1].",".$row[2].",".$three.",".$four.",".$row[5].",\"".$row[6]."\"\n";
}

mysql_free_result($result);
echo "\n";






echo "Agency Call Records\n";
$qstring = "SELECT QA.Caller.Call_Date, QA.Caller.Reference_Number, QA.Taxonomies.Taxonomy_Description, QA.Agencies.Agency_Description, QA.Contacted.Contacted_Description, QA.Time.Time_Description, QA.Assistance.Assistance_Description, QA.AgencyFeedback.Explanation, QA.Treated.Treated_Description,
QA.211Feedback.Appropriate, QA.Refer.Refer_Description, QA.211Feedback.Comments FROM  QA.Caller, QA.Taxonomies, QA.AgencyFeedback, QA.Agencies, QA.Contacted, QA.Time, QA.Assistance, QA.Treated, QA.211Feedback, QA.Refer
WHERE QA.AgencyFeedback.Contacted = QA.Contacted.Reference_Number AND QA.AgencyFeedback.Time = QA.Time.Reference_Number AND
QA.AgencyFeedback.Assistance = QA.Assistance.Reference_Number AND QA.AgencyFeedback.Treated = QA.Treated.Reference_Number AND
QA.211Feedback.Refer=QA.Refer.Reference_Number AND QA.AgencyFeedback.Taxonomy = QA.Taxonomies.Reference_Number AND QA.Caller.Reference_Number=QA.AgencyFeedback.Caller_Reference
AND QA.Taxonomies.Reference_Number=QA.AgencyFeedback.Taxonomy AND QA.Caller.Reference_Number=QA.211Feedback.Caller_Reference
AND QA.AgencyFeedback.Agency = QA.Agencies.Reference_Number AND QA.Caller.Call_Date  BETWEEN \"".$start_date."\" AND \"".$end_date."\" ORDER BY QA.Caller.Call_Date ASC";
$result = mysql_query($qstring);
//echo "Query: ".$qstring."\n";
echo "Call Date, Caller Reference Number, Taxonomy, Agency, Contacted, Time to Contact, Assistance, Assistance Explanation, Treated, 211 Appropriate, 211 Would Refer, 211 Comments \n";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
	if($row[9]==1){
		$nine="Yes";
	}else{
		$nine="No";
	}
	echo $row[0].",".$row[1].",".$row[2].",\"".$row[3]."\",".$row[4].",".$row[5].",".$row[6].",\"".$row[7]."\",".$row[8].",".$nine.",".$row[10].",\"".$row[11]."\"\n";
}

mysql_free_result($result);
echo "\n";


echo "Timestamp:,".time()."\n";
echo "END OF FILE\n\n";
?>